package com.hbsi.dao.impl;

import com.hbsi.dao.StudentDao;
import com.hbsi.domain.Student;
import com.hbsi.utils.ConnectionFactory;
import com.hbsi.utils.DBClose;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 程奕红
 * @version 1.0
 */
public class StudentDaoImpl implements StudentDao {
    //全局变量有初始值
    Connection connection;
    PreparedStatement preparedStatement;
    Statement statement;
    ResultSet res;

    @Override
    public List<Student> getAllStudents() {

        List<Student> list=new ArrayList<>();
        connection= ConnectionFactory.getConnection();
        try {
            statement=connection.prepareStatement("select  * from student");
          res= statement.executeQuery("select  * from student");
            while(res.next()){
                Student student=new Student();
                student.setSid(res.getString(1));
                student.setSname(res.getString(2));
                student.setSclass(res.getString(3));
                student.setAddress(res.getString(4));
                student.setTeleNumber(res.getString(5));
                student.setQQ(res.getString(6));
               list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }
//根据id查询
    @Override
    public Student getStudentById(String name) throws SQLException {
       List<Student> list=new ArrayList<>();
        connection= ConnectionFactory.getConnection();

        connection= ConnectionFactory.getConnection();
        try {
            statement=connection.prepareStatement("select  * from student sname like '%"+"%'");//用字符串拼接的方式
            res=statement.executeQuery("selelct * from student where name li");
            while(res.next()){
                Student student=new Student();
                student.setSid(res.getString(1));
                student.setSname(res.getString(2));
                student.setSclass(res.getString(3));
                student.setAddress(res.getString(4));
                student.setTeleNumber(res.getString(5));
                student.setQQ(res.getString(6));
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBClose.release(res,statement, (com.mysql.jdbc.Connection) connection);
        }

        return (Student) list;

    }

    @Override
    public List<Student> getStudentByName(String name) throws SQLException {

        List<Student> list=new ArrayList<>();
        connection= ConnectionFactory.getConnection();

        connection= ConnectionFactory.getConnection();
        try {
            statement=connection.prepareStatement("select  * from student sname like '%"+"%'");//用字符串拼接的方式
            res=statement.executeQuery("selelct * from student where name li");
            while(res.next()){
                Student student=new Student();
                student.setSid(res.getString(1));
                student.setSname(res.getString(2));
                student.setSclass(res.getString(3));
                student.setAddress(res.getString(4));
                student.setTeleNumber(res.getString(5));
                student.setQQ(res.getString(6));
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBClose.release(res,statement, (com.mysql.jdbc.Connection) connection);
        }

          return (List<Student>) list;

    }
//增
    @Override
    public boolean addStudent(Student s) throws SQLException {
        boolean flag=false;
        int n=0;
        try {
            connection=ConnectionFactory.getConnection();
            statement=connection.prepareStatement("insert into users(sid,sname,sclass,adress,teleNumber,QQ) values(?,?,?,?,?,?)");
          preparedStatement.setString(1,s.getSid());
            preparedStatement.setString(2,s.getSname());
            preparedStatement.setString(3,s.getSclass());
            preparedStatement.setString(4,s.getAddress());
            preparedStatement.setString(5,s.getTeleNumber());
            preparedStatement.setString(6,s.getQQ());
         //java.util.Date           java.sql.Date
            n=preparedStatement.executeUpdate();
            if(n>0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBClose.release(statement,connection);
        }


    }

    @Override
    public boolean updateStudent(Student s) {
        return false;
    }

    @Override
    public boolean deleteStudent(Student s) throws SQLException {
            boolean flag;
            connection=ConnectionFactory.getConnection();
            //定义删除的SQL语句
        String sql="delete from student where sid=1";
        int n=0;
        try {

            statement=connection.prepareStatement("delete from users where id=?");
            preparedStatement=connection.prepareStatement("delete * from student where id=?");
            n=preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBClose.release(statement,connection);
        }

        return false;
    }
}
